IF OBJECT_ID('dbo.vwMasterProposalsInfo') IS NOT NULL
BEGIN
    DROP VIEW dbo.vwMasterProposalsInfo
    IF OBJECT_ID('dbo.vwMasterProposalsInfo') IS NOT NULL
        PRINT '<<< FAILED DROPPING VIEW dbo.vwMasterProposalsInfo >>>'
    ELSE
        PRINT '<<< DROPPED VIEW dbo.vwMasterProposalsInfo >>>'
END
go
-- =======================================================================
-- Object Name: (VIEW) dbo.vwMasterProposalsInfo
--
-- Author:    Chris Bernard, Parallax Consulting LLC
--
-- Create date: 12/9/2008 
--
-- Description: View to MASTER Proposal with key lookuop valoues
--
-- Used BY Procedure/Report: (MOST)
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 12/9/08     CB        Created
-- 02/27/08    CB        Modified view to change what is returned for SponsorAgreementId
-- 06/01/2009 aab		modified view to change code to codeid where pt.arr_name = 'PROP_TYPE' 
-- =======================================================================
CREATE VIEW dbo.vwMasterProposalsInfo
AS	

	SELECT p.inst_code AS InstCode,
		   p.inst_no AS ProposalNumber,
	       p.prop_no AS PropNo,
		   CASE inst.int_unit_code 
				WHEN '1200' then 'MGH' 
				WHEN '2200' then 'BWH' 
				WHEN '1700' then 'IHP' 
				WHEN '1400' then 'SRH' 
				WHEN '1600' then 'MCL' 
				WHEN '0100' then 'PHS' 
				WHEN '2810' then 'FH' 
				ELSE ISNULL(inst.int_unit_code,'') END AS Institution, 
			dept.int_unit_code AS DeptartmentChiefCode,
			dept.unit_name AS Department,
		    un.int_unit_code AS UnitChiefCode,			
			propunit.unit_name AS Unit,
			RTRIM(r.lName) + ', ' + RTRIM(r.fName) + RTRIM(IsNull(' ' + r.mi,'')) AS PrincipalInvestigator,
			RTRIM(r.lName) + RTRIM(r.fName) + RTRIM(IsNull(' ' + r.mi,'')) AS PISearchName,
			p.spon_awd,
			p.spon_id,
			ISNULL(ISNULL(p.spon_awd,p.spon_id),'')  as SponsorAgreementId,
            sp.spon_code AS SponCode,
			spon_name AS SponsorName,
			st.code_seq AS SponsorTypeCode,
			st.code_desc AS SponsorType,
			p.pp_st_dt AS ProjectStartDate,
			p.pp_end_dt AS ProjectEndDate,
	        ps.code_desc AS ProposalStatus,
	        p.prop_stat AS PropStat,
		    p.cst_v AS VoluntaryCommitted,
			p.cst_m AS Mandatory,
			p.cst_c AS MandatoryandVoluntaryCommitted
   	FROM proposal p
		 INNER JOIN	PropPds pd ON p.prop_no = pd.prop_no AND p.inst_code = pd.inst_code AND pd.first_pd = 1
	     INNER JOIN	Faculty r ON  pd.Unique_ID = r.Unique_ID AND p.inst_code = r.inst_code
		 INNER JOIN codetab pt ON pt.codeid = p.prop_type AND	pt.inst_code = p.inst_code AND pt.arr_name = 'PROP_TYPE' 
         INNER JOIN	CodeTab ps ON p.prop_stat = ps.codeid 
		 LEFT OUTER JOIN Sponspas sp ON	p.spon_code = sp.spon_code AND p.inst_code = sp.inst_code
		 INNER JOIN	CodeTab st ON p.inst_code = st.inst_code AND sp.Spon_type = st.Code AND st.arr_name = 'SPON_TYPE' 
         INNER JOIN Unit un ON p.inst_code = un.inst_code AND p.unit_code = un.unit_code
         INNER JOIN Unit inst ON inst.inst_code = un.inst_code AND LEFT(un.deptid,4) = inst.deptID AND LEN(inst.deptID) = 4
	     LEFT OUTER JOIN Unit dept ON dept.inst_code = un.inst_code AND LEFT(un.deptid,7) = dept.deptID AND LEN(dept.deptID) = 7
	     LEFT OUTER JOIN Unit propunit ON propunit.inst_code = un.inst_code AND LEFT(un.deptid,10) = propunit.deptID AND LEN(propunit.deptID) = 10
   WHERE LEN(p.inst_no) = 11 AND p.system = 'pt' AND 
         p.inst_code = 'partners' AND 
         pt.codeid ='PARTNERS_P_PROP_TYPE_2' --Master
         
GO

IF OBJECT_ID('dbo.vwMasterProposalsInfo') IS NOT NULL
    PRINT '<<< CREATED VIEW dbo.vwMasterProposalsInfo >>>'
ELSE
    PRINT '<<< FAILED CREATING VIEW dbo.vwMasterProposalsInfo >>>'
GO

IF  EXISTS
    (SELECT name
     FROM sys.database_principals
     WHERE name = 'InsightReport')
BEGIN
	GRANT SELECT ON dbo.vwMasterProposalsInfo TO InsightReport
END

go

